import subprocess
import datetime

# 定义要分析的二进制日志文件路径和MySQL服务器的连接信息
binlog_file = 'mysql-bin.000002'
host = 'localhost'
user = 'root'
password = 'password'

# 构建mysqlbinlog命令行参数，输出格式为ROW
cmd = ['mysqlbinlog', '--base64-output=DECODE-ROWS', '--verbose', binlog_file]

# 如果指定了MySQL服务器连接信息，则添加到命令行参数中
# if host:
#     cmd.extend(['--host', host])
# if user:
#     cmd.extend(['--user', user])
# if password:
#     cmd.extend(['--password', password])

# 运行mysqlbinlog命令，获取输出并解析事务大小、执行时间和DML操作统计信息
output = subprocess.check_output(cmd)
tx_info_list = []
for line in output.decode().split('\n'):
    if line.startswith('###'):
        parts = line.split(' ')
        tx_dml_stats = {}
        if len(parts) >= 4:
            tx_id = parts[1]
            tx_start_time = parts[3] + ' ' + parts[4][:-1]
            tx_end_time = ''
            tx_dml_stats = {}
        elif len(parts) == 2 and parts[1] == 'COMMIT':
            tx_end_time = parts[0][4:] + ' ' + parts[1][:-1]
            total_dml_count = sum(tx_dml_stats.values())
            tx_info_list.append((tx_id, tx_start_time, tx_end_time, total_dml_count, tx_dml_stats))
        elif line.startswith('### UPDATE'):
            table_name = line.split(' ')[-1]
            tx_dml_stats[table_name] = tx_dml_stats.get(table_name, {'INSERT': 0, 'UPDATE': 0, 'DELETE': 0})
            tx_dml_stats[table_name]['UPDATE'] += 1
        elif line.startswith('### DELETE'):
            table_name = line.split(' ')[-1]
            tx_dml_stats[table_name] = tx_dml_stats.get(table_name, {'INSERT': 0, 'UPDATE': 0, 'DELETE': 0})
            tx_dml_stats[table_name]['DELETE'] += 1
        elif line.startswith('### INSERT'):
            table_name = line.split(' ')[-1]
            tx_dml_stats[table_name] = tx_dml_stats.get(table_name, {'INSERT': 0, 'UPDATE': 0, 'DELETE': 0})
            tx_dml_stats[table_name]['INSERT'] += 1

# 打印事务信息
for tx_info in tx_info_list:
    tx_id, tx_start_time, tx_end_time, total_dml_count, tx_dml_stats = tx_info
    tx_duration = (datetime.datetime.strptime(tx_end_time, '%Y-%m-%d %H:%M:%S') -
                   datetime.datetime.strptime(tx_start_time, '%Y-%m-%d %H:%M:%S')).total_seconds()
    print(f"事务ID：{tx_id}, 事务开始时间：{tx_start_time}, 耗时：{tx_duration:.2f}秒, dml总数：{total_dml_count}")
    for table_name, dml_stat in tx_dml_stats.items():
        insert_count = dml_stat.get('INSERT', 0)
        update_count = dml_stat.get('UPDATE', 0)
        delete_count = dml_stat.get('DELETE', 0)

